# SQLGenerator class takes the attributes of 'toc', 'db', 'answer_array', 'patient_conditions', 'conditions_', 'time_val', 'time_ent', 'ward', 'categoric_array'
# essential method of this class is generateSQL() which generates the query from user inputs

import condition
import conditions
import SyntaxParser

class SQLGenerator:
    def __init__(self, toc, db, groupevents_answers, patientevents_answers, chartevents_answers, medevents_answers, labevents_answers, patient_conditions, chartevents_conditions, medevents_conditions, labevents_conditions, diagnosis_pid_array, time_val, time_ent, ward, categoric_array):
        self.toc = toc
        self.db = db

        # group_answers refers to the pre-defined groups such as sedation, inotropes, etc.
        self.groupevents_answers = groupevents_answers

        # list of all answer types
        self.patientevents_answers = patientevents_answers
        self.chartevents_answers = chartevents_answers
        self.medevents_answers = medevents_answers
        self.labevents_answers = labevents_answers

	# list of all condition types
        self.patient_conditions = patient_conditions
        self.chartevents_conditions = chartevents_conditions
        self.medevents_conditions = medevents_conditions
        self.labevents_conditions = labevents_conditions
        
        self.diagnosis_pid_array = diagnosis_pid_array
        
        self.time_val = time_val
        self.time_ent = time_ent
        self.ward = ward

        self.categoric_array = categoric_array

    def getTOC(self):
	return self.toc

    def getDB(self):
	return self.db

    def getGroupAnswers(self):
	return self.groupevents_answers

    def getPatientEventsAnswers(self):
	return self.patientevents_answers

    def getChartEventsAnswers(self):
	return self.chartevents_answers

    def getMedEventsAnswers(self):
	return self.medevents_answers

    def getLabEventsAnswers(self):
	return self.labevents_answers

    def getPatientEventsConditions(self):
	return self.patient_conditions

    def getChartEventsConditions(self):
	return self.chartevents_conditions

    def getMedEventsConditions(self):
	return self.medevents_conditions

    def getLabEventsConditions(self):
	return self.labevents_conditions

    def getAnd_conditions(self, event_type):
        if event_type == "chartevents":
            return self.chartevents_conditions.getAnd_conditions()
        elif event_type == "medevents":
            return self.medevents_conditions.getAnd_conditions()
        elif event_type == "labevents":
            return self.labevents_conditions.getAnd_conditions()

    def getOr_conditions(self, event_type):
        if event_type == "chartevents":
            return self.chartevents_conditions.getOr_conditions()
        elif event_type == "medevents":
            return self.medevents_conditions.getOr_conditions()
        elif event_type == "labevents":
            return self.labevents_conditions.getOr_conditions()
    
    def getDiagnosisPidArray(self):
    	return self.diagnosis_pid_array

    def getTime_val(self):
	return self.time_val

    def getTime_ent(self):
	return self.time_ent
    
    def getWard(self):
	return self.ward

    def getCategoricArray(self):
        return self.categoric_array

# End of get / set functions
# --------------------------------------------------------------------------------------------- #
# Start of archival section

    # convert to SQL for archive database,
    # answerEventType refers to the type of the answer, such as a patientEvent (mrn), chartEvent (HR), etc.
    # index refers to the index of the answer
    # half: '<' refers to pid < 10000, '>=' refers to pid >= 10001, '<>' refers to every pid (default)
    def generateArchiveSQL(self, answerEventType, index, half="<>"):

        # time1 is 48 hours ago, time2 is 'duration' hours ago
        # format of time = "2007-08-06-20"
        now = SyntaxParser.getCurrentTime()
        
        time1 = SyntaxParser.subTime(now, 48, "hours")
        year = SyntaxParser.getTimeEnt(time1,"year")
        month = SyntaxParser.getTimeEnt(time1,"month")
        day = SyntaxParser.getTimeEnt(time1,"day")
        hour = SyntaxParser.getTimeEnt(time1,"hour")
        time1 = SyntaxParser.toArchiveTime(year,month,day,hour)
        #print "time1: " + str(time1)

        then = SyntaxParser.subTime(now, int(self.time_val), self.time_ent)
        year = SyntaxParser.getTimeEnt(then,"year")
        month = SyntaxParser.getTimeEnt(then,"month")
        day = SyntaxParser.getTimeEnt(then,"day")
        hour = SyntaxParser.getTimeEnt(then,"hour")
        time2 = SyntaxParser.toArchiveTime(year, month, day, hour)
        #print "time2: " + str(time2)

        # get the answer part of query based on answerEventType and index
        if answerEventType == "groupevents":
            answer = self.groupevents_answers[index]
        elif answerEventType == "chartevents":
            answer = self.chartevents_answers[index]
        elif answerEventType == "patientevents":
            answer = self.patientevents_answers[index]
        elif answerEventType == "medevents":
            answer = self.medevents_answers[index]
        elif answerEventType == "labevents":
            answer = self.labevents_answers[index]

        ans_med_ent = answer.getVariable()
        ref_ent_array = answer.getRef_fn()
        stat_ent_array = answer.getStat_fn()

        count = 0 # keeps track of the number of conditions in the query, needed to determine the number of ')'
        query_string = ""
        current_table = "" # keeps track of the current table in the SQL

        # Generate the SQL for groupevents
        if answerEventType == "groupevents":
            if ans_med_ent == "sedation" or ans_med_ent == "inotrope" or ans_med_ent == "antibiotic" or ans_med_ent == "thromboembolic prophylaxis":
                query_string = "SELECT D_Patients.mrn, D_MedItems.label, MedEvents.dose, MedEvents.charttime \
FROM D_MedItems, MedEvents, D_Patients \
WHERE MedEvents.itemid = D_MedItems.itemid AND MedEvents.pid = D_Patients.pid \
AND MedEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') \
AND MedEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24') \
AND lower(D_MedItems.label) IN "

                # Add the list of terms in query
                # E.g. if ans_med_ent = 'sedation', then it adds ('Propofol','Fentanyl',...) to the query
                # else if ans_med_ent = 'inotrope', then it adds ('2mg Adrenaline','Milrinone',...) to the query
                query_string = query_string + SyntaxParser.addTerms(ans_med_ent)

                # Takes care of Care Unit Ward
                query_string = query_string + self.getCareUnit("medevents")

                # Takes care of pid, used only to speed up query
                query_string = query_string + self.getPid("medevents", half)

                current_table = "medEvents"

        # Generate the SQL for chartevents or labevents
        elif answerEventType == "chartevents" or answerEventType == "labevents":
            query_string = "SELECT D_Patients.mrn, chartEvents.value1, chartEvents.charttime FROM chartEvents, D_ChartItems, D_Patients WHERE \
chartEvents.itemid = D_ChartItems.itemid AND chartEvents.pid = D_Patients.pid AND lower(D_ChartItems.label) = '"
            query_string = query_string + ans_med_ent + "' "
            query_string = query_string + "AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

            # Takes care of Care Unit Ward
            query_string = query_string + self.getCareUnit("chartevents")

            # Takes care of pid, used only to speed up query
            query_string = query_string + self.getPid("chartevents", half)

            current_table = "chartEvents"

        # Generate the SQL for patientevents
        elif answerEventType == "patientevents":
            query_string = "SELECT D_Patients." + ans_med_ent + " FROM D_Patients, CensusEvents WHERE \
D_Patients.pid = CensusEvents.pid"

            # Takes care of Care Unit Ward
            query_string = query_string + self.getCareUnit("patientevents")

            # Takes care of pid, used only to speed up query
            query_string = query_string + self.getPid("patientevents", half)

            current_table = "D_Patients"

        # Generate the SQL for medevents
        elif answerEventType == "medevents":
            query_string = "SELECT D_Patients.mrn, medEvents.dose, medEvents.charttime FROM medEvents, D_MedItems, D_Patients WHERE \
medEvents.itemid = D_MedItems.itemid AND medEvents.pid = D_Patients.pid AND lower(D_MedItems.label) = '"
            query_string = query_string + ans_med_ent + "' "
            query_string = query_string + "AND medEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
medEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

            # Takes care of Care Unit Ward
            query_string = query_string + self.getCareUnit("medevents")

            # Takes care of pid, used only to speed up query
            query_string = query_string + self.getPid("medevents", half)

            current_table = "medEvents"

        # patient event condition 
        if self.patient_conditions is not None and len(self.patient_conditions) > 0:              
            
            query_string = query_string + " AND " + current_table + ".pid"
            
            # get each patient_condition of query
            query_string = query_string + " IN (SELECT pid FROM D_Patients WHERE "
            patient_condition = self.patient_conditions[0]

            # Check if the attribute is dob, which is treated differently to other attributes as it is a date
            if patient_condition.getAtt() == "age":
                query_string = query_string + "to_char(dob,'YYYY-MM-DD') " + patient_condition.getOppComp() + "'" + str(SyntaxParser.subTime(SyntaxParser.getCurrentTime(), 52*int(patient_condition.getVal()), "weeks")) + "'"
            else:
                query_string = query_string + patient_condition.getAtt() + patient_condition.getComp() + "'" + str(patient_condition.getVal()) + "'"

            h = 1
            while h != len(self.patient_conditions):
                patient_condition = self.patient_conditions[h]

                # Check if the attribute is dob, which is treated differently to other attributes as it is a date
                if patient_condition.getAtt() == "age":
                    query_string = query_string + ' ' + patient_condition.getType() + " to_char(dob,'YYYY-MM-DD') " + patient_condition.getOppComp() + "'" + str(SyntaxParser.subTime(SyntaxParser.getCurrentTime(), 52*int(patient_condition.getVal()), "weeks")) + "'"
                else:
                    query_string = query_string + " " + patient_condition.getType() + " " + patient_condition.getAtt() + patient_condition.getComp() + "'" + str(patient_condition.getVal()) + "'"
                h = h + 1

            current_table = "D_Patients"

        # chart event condition
        i = 0
        j = 0
        if self.getChartEventsConditions() is not None and (len(self.getChartEventsConditions().getAnd_conditions()) > 0 or len(self.getChartEventsConditions().getOr_conditions()) > 0):
            # get each and_condition of query
            while i != len(self.getAnd_conditions("chartevents")):
                con = self.getAnd_conditions("chartevents")[i]

                query_string = query_string + " AND " + current_table + ".pid"

                query_string = query_string + " IN (SELECT chartEvents.pid FROM \
chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                query_string = query_string + con.getAtt() + "' AND chartevents.value1 " + con.getComp() + "'" + con.getVal() + "'"
                query_string = query_string + " AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("chartevents")

                i = i + 1
                count = count + 1
            
            # get each or_condition of query
            while j != len(self.getOr_conditions("chartevents")):

                query_string = query_string + " AND (" + current_table + ".pid"
                
                query_string = query_string + " IN (SELECT chartEvents.pid FROM \
chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                con = self.getOr_conditions("chartevents")[j]
                con1 = con.getCondition1()
                con2 = con.getCondition2()

                query_string = query_string + con1.getAtt() + "' AND chartevents.value1 " + con1.getComp() + "'" + con1.getVal() + "'"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("chartevents")

                query_string = query_string + " AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"
                
                query_string = query_string + " OR "

                query_string = query_string + ' ' + current_table + ".pid"

                query_string = query_string + " IN (SELECT chartEvents.pid FROM \
chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                query_string = query_string + con2.getAtt() + "' AND chartevents.value1 " + con2.getComp() + "'" + con2.getVal() + "'"

                # Takes care of Care Unit Ward
                #query_string = query_string + self.getCareUnit("chartevents")

                query_string = query_string + " AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24'))"
                j = j + 1
                count = count + 2 # each or-condition needs 1 more ')' than for and-condition

            current_table = "chartEvents"

        # medication event condition
        i = 0
        j = 0
        if self.getMedEventsConditions() is not None and (len(self.getMedEventsConditions().getAnd_conditions()) > 0 or len(self.getMedEventsConditions().getOr_conditions()) > 0):
            # get each and_condition of query
            while i != len(self.getAnd_conditions("medevents")):
                con = self.getAnd_conditions("medevents")[i]

                query_string = query_string + " AND " + current_table + ".pid"
                
                query_string = query_string + " IN (SELECT medevents.pid FROM \
medEvents, D_MedItems WHERE medEvents.itemid = D_MedItems.itemid AND lower(D_MedItems.label) = '"
                query_string = query_string + con.getAtt() + "' AND medevents.dose " + con.getComp() + "'" + con.getVal() + "'"
                query_string = query_string + " AND medEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
medEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")
                
                i = i + 1
                count = count + 1 
            
            # get each or_condition of query
            while j != len(self.getOr_conditions("medevents")):

                query_string = query_string + " AND (" + current_table + ".pid"
                
                query_string = query_string + " IN (SELECT medEvents.pid FROM \
medEvents, D_MedItems WHERE medEvents.itemid = D_MedItems.itemid AND lower(D_MedItems.label) = '"
                con = self.getOr_conditions("medevents")[j]
                con1 = con.getCondition1()
                con2 = con.getCondition2()

                query_string = query_string + con1.getAtt() + "' AND medevents.dose " + con1.getComp() + "'" + con1.getVal() + "'"

                query_string = query_string + " AND MedEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
medEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")

                
                query_string = query_string + " OR "

                query_string = query_string + ' ' + current_table + ".pid"

                query_string = query_string + " IN (SELECT medEvents.pid FROM \
medEvents, D_MedItems WHERE medEvents.itemid = D_MedItems.itemid AND lower(D_MedItems.label) = '"
                query_string = query_string + con2.getAtt() + "' AND medevents.dose " + con2.getComp() + "'" + con2.getVal() + "'"

                query_string = query_string + " AND MedEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
medEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24'))"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")
                
                j = j + 1
                count = count + 2 # each or-condition needs 1 more ')' than for and-condition

            current_table = "medevents"

        # laboratory event condition
        i = 0
        j = 0
        if self.getLabEventsConditions() is not None and (len(self.getLabEventsConditions().getAnd_conditions()) > 0 or len(self.getLabEventsConditions().getOr_conditions()) > 0):
            # get each and_condition of query
            while i != len(self.getAnd_conditions("labevents")):
                con = self.getAnd_conditions("labevents")[i]

                query_string = query_string + " AND " + current_table + ".pid"
                
                query_string = query_string + " IN (SELECT chartevents.pid FROM \
chartevents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                query_string = query_string + con.getAtt() + "' AND chartevents.value1 " + con.getComp() + "'" + con.getVal() + "'"
                query_string = query_string + " AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")
                
                i = i + 1
                count = count + 1 
            
            # get each or_condition of query
            while j != len(self.getOr_conditions("labevents")):

                query_string = query_string + " AND (" + current_table + ".pid"
                
                query_string = query_string + " IN (SELECT chartEvents.pid FROM \
chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                con = self.getOr_conditions("labevents")[j]
                con1 = con.getCondition1()
                con2 = con.getCondition2()

                query_string = query_string + con1.getAtt() + "' AND chartEvents.value1 " + con1.getComp() + "'" + con1.getVal() + "'"

                query_string = query_string + " AND chartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24')"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")

                
                query_string = query_string + " OR "

                query_string = query_string + ' ' + current_table + ".pid"

                query_string = query_string + " IN (SELECT chartEvents.pid FROM \
chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = '"
                query_string = query_string + con2.getAtt() + "' AND chartEvents.value1 " + con2.getComp() + "'" + con2.getVal() + "'"

                query_string = query_string + " AND ChartEvents.charttime >= to_date('" + time2 + "','YYYY-MM-DD-HH24') AND \
chartEvents.charttime < to_date('" + time1 + "','YYYY-MM-DD-HH24'))"

                # Takes care of Care Unit Ward
                # query_string = query_string + self.getCareUnit("medevents")
                
                j = j + 1
                count = count + 2 # each or-condition needs 1 more ')' than for and-condition

            current_table = "chartevents"

        #diagnosis events
        pid_array = self.getDiagnosisPidArray()
        if pid_array is not None:
	        if len(pid_array) > 0:
	            # get each and_condition of query

                    query_string = query_string + " AND " + current_table + ".pid"
                    
                    query_string = query_string + " IN ('" + str(pid_array[0][0]) + "'"
                    for index in range(1, len(pid_array)):
                            query_string = query_string + ", '" + str(pid_array[index][0]) + "'"
                    query_string += ")"
	        else:
	        	return -1
        """    
        #prev diagnosis events
        pid_array = self.getPrevDiagnosisPidArray()
        if pid_array is not None:
	        if len(pid_array) > 0:
	            # get each and_condition of query

                    query_string = query_string + " AND " + current_table + ".pid"
                    
                    query_string = query_string + " IN ('" + str(pid_array[0][0]) + "'"
                    for index in range(1, len(pid_array)):
                            query_string = query_string + ", '" + str(pid_array[index][0]) + "'"
                    query_string += ")"
	        else:
	        	return -1
               
        #curr diagnosis events
        pid_array = self.getCurrDiagnosisPidArray()
        
        if pid_array is not None:
	        if len(pid_array) > 0:
	            # get each and_condition of query
	
                    query_string = query_string + " AND " + current_table + ".pid"
                    
                    query_string = query_string + " IN ('" + str(pid_array[0][0]) + "'"
                    for index in range(1, len(pid_array)):
                            query_string = query_string + ", '" + str(pid_array[index][0]) + "'"
                    query_string += ")"
	        else:
	        	return -1
        """
        if self.patient_conditions is not None and len(self.patient_conditions) > 0:
            query_string = query_string + ")"

        k = 0
        while k < count:
            query_string = query_string + ")" # add ')' for count times 
            k = k + 1

        # Sort the result
        if answerEventType == "groupevents":
            #if ans_med_ent == "sedation" or ans_med_ent == "inotrope" or ans_med_ent == "antibiotic" or ans_med_ent == "thromboembolic prophylaxis":
            query_string = query_string + " ORDER BY D_Patients.mrn, D_MedItems.label, MedEvents.charttime"
        elif answerEventType == "chartevents" or answerEventType == "labevents":
            query_string = query_string + " ORDER BY D_Patients.mrn, chartEvents.charttime"
        elif answerEventType == "medevents":
            query_string = query_string + " ORDER BY D_Patients.mrn, medEvents.charttime"
        
        return query_string

    # generate the SQL query string for Care Unit in Archival db
    # CICU (1), NSICU (2), HDU (3), GICU (4)
    def getCareUnit(self, eventType):
        ward = self.getWard()
        if ward == "GICU":
            if eventType == "chartevents":
                return " AND chartevents.cuid = 4"
            elif eventType == "medevents":
                return " AND medevents.cuid = 4"
            elif eventType == "patientevents":
                return " AND censusevents.careUnit = 4"
        elif ward == "NSICU":
            if eventType == "chartevents":
                return " AND chartevents.cuid = 2"
            elif eventType == "medevents":
                return " AND medevents.cuid = 2"
            elif eventType == "patientevents":
                return " AND censusevents.careUnit = 2"
        elif ward == "CICU":
            if eventType == "chartevents":
                return " AND chartevents.cuid = 1"
            elif eventType == "medevents":
                return " AND medevents.cuid = 1"
            elif eventType == "patientevents":
                return " AND censusevents.careUnit = 1"
        elif ward == "HDU":
            if eventType == "chartevents":
                return " AND chartevents.cuid = 3"
            elif eventType == "medevents":
                return " AND medevents.cuid = 3"
            elif eventType == "patientevents":
                return " AND censusevents.careUnit = 3"

        return ""

    # generate the SQL query string for pid in Archival db, used to speed up the process
    # half: '<' refers to pid < 10000, '>=' refers to pid >= 10001, '<>' refers to every pid (default)
    def getPid(self, eventType, half):
        if half == "<":
            if eventType == "chartevents":
                return " AND chartevents.pid < 10000"
            elif eventType == "medevents":
                return " AND medevents.pid < 10000"
            elif eventType == "patientevents":
                return " AND censusevents.pid < 10000"
        elif half == ">=":
            if eventType == "chartevents":
                return " AND chartevents.pid >= 10000"
            elif eventType == "medevents":
                return " AND medevents.pid >= 10000"
            elif eventType == "patientevents":
                return " AND censusevents.pid >= 10000"
        elif half == "<>":
            return ""

        return ""

# End of archival section
# --------------------------------------------------------------------------------------------- #
# Start of realtime section

    # Convert to SQL for real time database
    # Due to performance issue, this is replaced by performing many sub-queries.
    # This means that each sub-query is used for each condition, and the results combined.
    '''
    def generateRealTimeSQL(self):

        #time1 = "2007-08-06 00:00:00"
        now = SyntaxParser.getCurrentTime()
        then = SyntaxParser.subTime(now, int(self.time_val), self.time_ent)
        year = SyntaxParser.getTimeEnt(then,"year")
        month = SyntaxParser.getTimeEnt(then,"month")
        day = SyntaxParser.getTimeEnt(then,"day")
        hour = SyntaxParser.getTimeEnt(then,"hour")
        time1 = SyntaxParser.toRealTime(year, month, day, hour)

        # get all the answer part of query
        for answer in self.answer_array:
            ans_med_ent = answer.getVariable()
            ref_ent_array = answer.getRef_fn()
            stat_ent_array = answer.getStat_fn()
            isCategoric = 0

            for categoric_term in self.categoric_array:
                if ans_med_ent == categoric_term:
                    isCategoric = 1
                    break

            query_string = ""
            
            if isCategoric == 1:
                query_string = "SELECT dbcodeconfig_set.longlabel FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.value1_cid=dbcodeconfig_set.oid AND ptevent.value1=dbcodeconfig_set.elemid AND ptevent.cid=pteventclass.oid AND \
lower(pteventclass.longlabel)='"
                query_string = query_string + ans_med_ent + "' "
                query_string = query_string + "AND ptevent.entrytime > '" + time1 + "'"

            else:
                query_string = "SELECT TO_NUMBER(ptevent.value1) FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid \
AND lower(pteventclass.longlabel) = '"
                query_string = query_string + ans_med_ent + "' " # need to implement patient_class
                query_string = query_string + "AND ptevent.entrytime > '" + time1 + "'" # need to implement entry time

            if self.patient_conditions is not None:              
                # get each patient_condition of query
                query_string = query_string + " AND ptevent.gprid IN (SELECT gprid FROM cfgpatients WHERE "
                for patient_condition in self.patient_conditions:
                    query_string = query_string + ' ' + patient_condition.getType().upper() + ' ' + patient_condition.getAtt() + patient_condition.getComp() + patient_condition.getVal()

            i = 0
            # get each and_condition of query
            while i != len(self.getAnd_conditions()):
                con = self.getAnd_conditions()[i]
                query_string = query_string + " AND ptevent.gprid IN ("
                if self.isNumeric(con):
                    # if it is, use toNumeric
                    query_string = query_string + self.toNumeric(con)
                else:
                    # else, use toCategoric
                    query_string = query_string + self.toCategoric(con)
                i = i + 1

            j = 0
            # get each or_condition of query
            while j != len(self.getOr_conditions()):
                query_string = query_string + " AND (ptevent.gprid IN ("
                con = self.getOr_conditions()[j]
                con1 = con.getCondition1()
                con2 = con.getCondition2()

                if self.isNumeric(con1):
                    # if it is, use toNumeric
                    query_string = query_string + self.toNumeric(con1)
                else:
                    # else, use toCategoric
                    query_string = query_string + self.toCategoric(con1)
                
                query_string = query_string + " OR ptevent.gprid IN ("

                if self.isNumeric(con2):
                    # if it is, use toNumeric
                    query_string = query_string + self.toNumeric(con2)
                else:
                    # else, use toCategoric
                    query_string = query_string + self.toCategoric(con2)

                query_string = query_string + ")"
                j = j + 1
                
        query_string = query_string + " AND ptevent.entrytime > '" + time1 + "'"        

        if self.patient_conditions is not None:
            query_string = query_string + ")"

        k = 0            
        while k < i+3*j:
            query_string = query_string + ")" # add n number of ')'
            k = k + 1
        
        return query_string
    '''

    # convert to SQL for a query on a single condition only, return gprid, used on realtime db
    def generateSingleQuery(self, condition, condition_type):
        now = SyntaxParser.getCurrentTime()
        then = SyntaxParser.subTime(now, int(self.time_val), self.time_ent)
        year = SyntaxParser.getTimeEnt(then,"year")
        month = SyntaxParser.getTimeEnt(then,"month")
        day = SyntaxParser.getTimeEnt(then,"day")
        hour = SyntaxParser.getTimeEnt(then,"hour")
        time1 = SyntaxParser.toRealTime(year, month, day, hour)

        if condition_type == "chartevents":
            if self.isNumeric(condition):
                # Some variables, like temperature, is stored as value2; all the other variables are stored as value1
                if condition.getVal() == "temp. site/value":
                    query_string = self.getChartEvent_Num(condition, 2)
                else:
                    query_string = self.getChartEvent_Num(condition, 1)
            else:
                query_string = self.getChartEvent_Cat(condition)
            query_string = query_string + " AND ptevent.entrytime > '" + time1 + "'" 
        elif condition_type == "medevents":
            query_string = self.getMedEvent(condition)
            query_string = query_string + " AND iovolumeevent.entrytime > '" + time1 + "'"
        elif condition_type == "labevents":
            query_string = self.getLabEvent(condition)
            query_string = query_string + " AND labevent.entrytime > '" + time1 + "'"
        
        return query_string

    # convert to final sql query based on the answer wanted and the condition set
    # condition type is the type of the answer wanted (chartevent, medevent, etc)
    # condition set is a list of gprid, index refers to the index of the answer array
    def generateFinalQuery(self, answer_type, condition_set, index):

        if condition_set is None:
            print ''
        elif len(condition_set) == 0:
            return -1

        now = SyntaxParser.getCurrentTime()
        then = SyntaxParser.subTime(now, int(self.time_val), self.time_ent)
        year = SyntaxParser.getTimeEnt(then,"year")
        month = SyntaxParser.getTimeEnt(then,"month")
        day = SyntaxParser.getTimeEnt(then,"day")
        hour = SyntaxParser.getTimeEnt(then,"hour")
        time1 = SyntaxParser.toRealTime(year, month, day, hour)

        if answer_type == "groupevents":
            answer = self.groupevents_answers[index]
        elif answer_type == "patientevents":
            answer = self.patientevents_answers[index]
        elif answer_type == "chartevents":
            answer = self.chartevents_answers[index]
        elif answer_type == "medevents":
            answer = self.medevents_answers[index]
        elif answer_type == "labevents":
            answer = self.labevents_answers[index]
        
        ans_med_ent = answer.getVariable()
        ref_ent_array = answer.getRef_fn()
        stat_ent_array = answer.getStat_fn()

        if answer_type == "groupevents":
            if ans_med_ent == "sedation" or ans_med_ent == "inotrope":
                query_string = "SELECT cfgpatients.medrecnum, ivdripinfitem.alternatelabel, iovolumeevent.volume, iovolumeevent.entrytime \
FROM cfgpatients, iovolumeevent, ivdripinfitem WHERE \
cfgpatients.gprid = iovolumeevent.gprid AND cfgpatients.gprid = ivdripinfitem.gprid \
AND iovolumeevent.item_cid = ivdripinfitem.cid AND iovolumeevent.item_oid = ivdripinfitem.oid \
AND iovolumeevent.gprid = ivdripinfitem.gprid AND lower(ivdripinfitem.alternatelabel) IN "

                # Add the list of terms in query
                # E.g. if ans_med_ent = 'sedation', then it adds ('Propofol','Fentanyl',...) to the query
                # else if ans_med_ent = 'inotrope', then it adds ('2mg Adrenaline','Milrinone',...) to the query
                query_string = query_string + SyntaxParser.addTerms(ans_med_ent)
                query_string = query_string + " AND iovolumeevent.entrytime > '" + time1 + "'"
                if condition_set is not None:
                    query_string = query_string + " AND iovolumeevent.gprid IN ("

            elif ans_med_ent == "antibiotic" or ans_med_ent == "thromboembolic prophylaxis":
                query_string = "SELECT cfgpatients.medrecnum, pharmacyprod.longname, meddoseevent.dose, meddoseevent.entrytime \
FROM cfgpatients, meddoseevent, pharmacyprod WHERE \
cfgpatients.gprid = meddoseevent.gprid AND meddoseevent.route_oid = pharmacyprod.oid \
AND lower(pharmacyprod.longname) IN "

                # Add the list of terms in query
                # E.g. if ans_med_ent = 'antibiotic', then it adds ('Amikacin','Ampicillan',...) to the query
                query_string = query_string + SyntaxParser.addTerms(ans_med_ent)
                query_string = query_string + " AND meddoseevent.entrytime > '" + time1 + "'"
                if condition_set is not None:
                    query_string = query_string + " AND meddoseevent.gprid IN ("
            
        elif answer_type == "chartevents":
            isCategoric = 0
            for categoric_term in self.categoric_array:
                if ans_med_ent == categoric_term:
                    isCategoric = 1
                    break
         
            if isCategoric == 1:
                query_string = "SELECT cfgpatients.medrecnum, dbcodeconfig_set.longlabel, ptevent.entrytime \
FROM ptevent, pteventclass, dbcodeconfig_set, cfgpatients WHERE \
cfgpatients.gprid = ptevent.gprid AND \
ptevent.value1_cid=dbcodeconfig_set.oid AND ptevent.value1=dbcodeconfig_set.elemid AND ptevent.cid=pteventclass.oid AND \
lower(pteventclass.longlabel)='"
                query_string = query_string + ans_med_ent + "' "
                query_string = query_string + "AND ptevent.entrytime > '" + time1 + "'"
            else:
                if ans_med_ent == 'temp. site/value':
                    query_string = "SELECT cfgpatients.medrecnum, TO_NUMBER(ptevent.value2), ptevent.entrytime \
FROM ptevent, pteventclass, cfgpatients WHERE ptevent.cid = pteventclass.oid AND cfgpatients.gprid = ptevent.gprid \
AND lower(pteventclass.longlabel) = '"
                else:
                    query_string = "SELECT cfgpatients.medrecnum, TO_NUMBER(ptevent.value1), ptevent.entrytime \
FROM ptevent, pteventclass, cfgpatients WHERE ptevent.cid = pteventclass.oid AND cfgpatients.gprid = ptevent.gprid \
AND lower(pteventclass.longlabel) = '"

                query_string = query_string + ans_med_ent + "' "
                query_string = query_string + "AND ptevent.entrytime > '" + time1 + "'"

            if condition_set is not None:
                query_string = query_string + " AND ptevent.gprid IN ("

        elif answer_type == "patientevents":
            query_string = "SELECT cfgpatients." + ans_med_ent + " FROM cfgpatients"
            if condition_set is not None:
                    query_string = query_string + " WHERE cfgpatients.gprid IN ("

        elif answer_type == "medevents":
            query_string = "SELECT cfgpatients.medrecnum, iovolumeevent.volume, iovolumeevent.entrytime \
FROM iovolumeevent, ivdripinfitem, cfgpatients WHERE cfgpatients.gprid = iovolumeevent.gprid AND \
iovolumeevent.item_cid = ivdripinfitem.cid AND iovolumeevent.item_oid = ivdripinfitem.oid \
AND iovolumeevent.gprid = ivdripinfitem.gprid AND lower(ivdripinfitem.alternatelabel) = '"
            query_string = query_string + ans_med_ent + "' "
            query_string = query_string + "AND iovolumeevent.entrytime > '" + time1 + "'"
            if condition_set is not None:
                query_string = query_string + " AND iovolumeevent.gprid IN ("

        elif answer_type == "labevents":
            query_string = "SELECT cfgpatients.medrecnum, TO_NUMBER(labevent.value1), labevent.entrytime \
FROM labevent, labeventclass, cfgpatients WHERE cfgpatients.gprid = labevent.gprid AND \
labevent.cid = labeventclass.oid AND lower(labeventclass.longlabel) = '"
            query_string = query_string + ans_med_ent + "' "
            query_string = query_string + "AND labevent.entrytime > '" + time1 + "'"
            if condition_set is not None:
                query_string = query_string + " AND labevent.gprid IN ("

        # add all the gprid from condition_set (which is the list of patients satisfying all conditions)
        if condition_set is not None:
            if len(condition_set) > 0:
                value = condition_set[0]
                query_string = query_string + "'" + str(value) + "'"
                i = 1
                while i != len(condition_set):
                    value = condition_set[i]
                    query_string = query_string + ",'" + str(value) + "'"
                    i = i + 1

            query_string = query_string + ")"

        if answer_type == "groupevents":
            if ans_med_ent == "sedation" or ans_med_ent == "inotrope":
                query_string = query_string + " ORDER BY cfgpatients.medrecnum, ivdripinfitem.alternatelabel, iovolumeevent.entrytime"
            elif ans_med_ent == "antibiotic" or ans_med_ent == "thromboembolic prophylaxis":
                query_string = query_string + " ORDER BY cfgpatients.medrecnum, pharmacyprod.longname, meddoseevent.entrytime"
        elif answer_type == "chartevents":
            query_string = query_string + " ORDER BY cfgpatients.medrecnum, ptevent.entrytime"
        elif answer_type == "medevents":
            query_string = query_string + " ORDER BY cfgpatients.medrecnum, iovolumeevent.entrytime"
        elif answer_type == "labevents":
            query_string = query_string + " ORDER BY cfgpatients.medrecnum, labevent.entrytime"
        
        return query_string


    # convert to SQL for numeric attribute of chart-event on the realtime db (such as HR) 
    def getChartEvent_Num(self, condition, ind):
        com = condition.getComp()
        att = condition.getAtt()
        val = condition.getVal()

        if ind == 1:
            query = "SELECT DISTINCT ptevent.gprid FROM ptevent, pteventclass WHERE \
ptevent.cid = pteventclass.oid AND lower(pteventclass.longlabel) = '" + att + "' AND ptevent.value1 " + com + ' %e' % (float(val))
        else:
            query = "SELECT DISTINCT ptevent.gprid FROM ptevent, pteventclass WHERE \
ptevent.cid = pteventclass.oid AND lower(pteventclass.longlabel) = '" + att + "' AND ptevent.value2 " + com + ' %e' % (float(val))
        
        return query

    # convert to SQL for categorical attribute of chart-event on the realtime db (such as Vent mode)
    def getChartEvent_Cat(self, condition):
        com = str(condition.getComp())
        att = str(condition.getAtt())
        val = str(condition.getVal())
        
        query = "SELECT DISTINCT ptevent.gprid FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND ptevent.value1 = dbcodeconfig_set.elemid \
AND lower(pteventclass.longlabel) = '" + att + "' AND lower(dbcodeconfig_set.shortlabel) " + com + " '" + val + "'"
        return query

    # convert to SQL for selecting patients according to demographic info provided (such as mrn) on the realtime db 
    def getPatientEvent(self):
        query_string = "SELECT DISTINCT gprid FROM cfgpatients WHERE "
        patient_condition = self.patient_conditions[0]
        query_string = query_string + patient_condition.getAtt() + patient_condition.getComp() + "'" + str(patient_condition.getVal()) + "'"
        i = 1
        while i != len(self.patient_conditions):
            patient_condition = self.patient_conditions[i]
            query_string = query_string + ' ' + patient_condition.getType().upper() + ' ' + patient_condition.getAtt() + patient_condition.getComp() + patient_condition.getVal()
            i = i + 1
        return query_string

    # convert to SQL for attribute in med-event (sedation / inotropes) on the realtime db 
    def getMedEvent(self, condition):
        com = str(condition.getComp())
        att = str(condition.getAtt())
        val = str(condition.getVal())
        
        query = "SELECT DISTINCT iovolumeevent.gprid FROM iovolumeevent, ivdripinfitem WHERE \
iovolumeevent.item_cid = ivdripinfitem.cid AND iovolumeevent.item_oid = ivdripinfitem.oid \
AND iovolumeevent.gprid = ivdripinfitem.gprid \
AND lower(ivdripinfitem.alternatelabel) = '" + att + "' AND iovolumeevent.volume " + com + ' %e' % (float(val))
        return query

    # convert to SQL for attribute in lab-event (such as calcium) on the realtime db 
    def getLabEvent(self, condition):
        com = str(condition.getComp())
        att = str(condition.getAtt())
        val = str(condition.getVal())
        
        query = "SELECT DISTINCT labevent.gprid FROM labevent, labeventclass WHERE \
labevent.cid = labeventclass.oid \
AND lower(labeventclass.longlabel) = '" + att + "' AND labevent.value1 " + com + ' %e' % (float(val))
        return query

# End of realtime section
# --------------------------------------------------------------------------------------------- #
# Useful functions used by the SQL Generator, independent to the queries.

    # return 1 if num_ent, return 0 if cat_ent
    def isNumeric(self, condition):
        val = condition.getVal()
        try:
            int(val)
            return 1
        except ValueError:
            return 0

    # convert to exponential format
    def toExp(self, value):
        temp = '%e' % (value)
        return temp
                    
        
